package com.sfy.les.dictionary.sqlserver;

import com.sfy.les.dictionary.DataBaseDictionary;
import com.sfy.les.dictionary.DictionaryInfo;
import com.sfy.les.table.TableInfo;
import com.sfy.les.utils.Common;

public class SqlServerDictionaryInfo extends DictionaryInfo{
	
	@Override
	public String getQueryAllTablesInfoSql(DataBaseDictionary exportParam) {
		String querySql = "use " + exportParam.getDbName();
		querySql += " select DISTINCT cast( o.name as varchar(500)) TABLE_NAME, cast( ep.value as varchar(500)) TABLE_DESC from sys.objects o";
		querySql += " LEFT JOIN sys.extended_properties ep on o.object_id=ep.major_id and ep.minor_id = 0 and ep.class = 1";
		querySql += " AND ep.name = 'MS_Description'";
		querySql += " WHERE o.type IN ('U','V')";
		if(!Common.checkNullOrEmpty(exportParam.getTableNamePrefix())){
			querySql += " AND o.name like '" + exportParam.getTableNamePrefix() + "%'";
		}
		return querySql;
	}

	@Override
	public String getQueryTableFieldsInfoSql(DataBaseDictionary exportParam, TableInfo tableInfo) {
		String querySql = "USE " + exportParam.getDbName()
		+ " SELECT  "
		+ "	  FIELD_NAME=cast( a.name as varchar(500)), "
		+ "	  FIELD_TYPE=cast( b.name as varchar(500)), "
		+ "	  FIELD_LENGTH=cast( COLUMNPROPERTY(a.id,a.name,'PRECISION') as varchar(500)), "
		+ "	  FIELD_DECIMAL_LENGTH=cast( isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as varchar(500)),"
		+ "	  FIELD_IS_EMPTY=cast( case when a.isnullable=1 then '允许' else '不允许' end as varchar(500)), "
		+ "	  FIELD_IS_PRIMARY_KEY=cast( case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in ("
		+ "	    SELECT name FROM sysindexes WHERE indid in("
		+ "	     SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid "
		+ "	     ))) then '是' else '否' end as varchar(500)), "
		+ "	  FIELD_IS_AUTOINCERE=cast(case when COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1 then '是' else '否' end as varchar(500)), "
		+ "	  FIELD_DEFAULT_VALUE=cast(isnull(e.text,'NULL') as varchar(500)),"
		+ "	  FIELD_DESC=cast(isnull(g.[value],'NULL') as varchar(500))"
		+ " FROM syscolumns a "
		+ " LEFT JOIN systypes b on a.xtype=b.xusertype "
		+ " INNER JOIN sysobjects d on a.id=d.id and d.xtype IN ('U','V') and d.name<>'dtproperties' "
		+ " LEFT JOIN syscomments e on a.cdefault=e.id "
		+ " LEFT JOIN sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id "
		+ " where d.name='" + tableInfo.getTableName() + "'"
		+ " ORDER BY a.id,a.colorder";
		return querySql;
	}

	@Override
	public String getQueryTableIndexInfoSql(DataBaseDictionary exportParam, TableInfo tableInfo) {
		String querySql = "use " + exportParam.getDbName()
		+ " SELECT INDEX_TYPE, INDEX_STYLE=NULL,INDEX_NAME,INDEX_FIELDS FROM"
		+ " ("
		+ " SELECT"
			+ " o.object_id,"
			+ " ic.index_id,"
			+ " o.name TABLE_NAME"
			+ " ,i.name INDEX_NAME"
			+ " ,CASE WHEN i.is_unique=1 THEN 'Unique' ELSE i.type_desc END INDEX_TYPE"
			+ " ,STUFF(("
		          + " SELECT distinct ',' + cc.name"
		          + " FROM sys.columns cc"
				  + " INNER JOIN sys.index_columns iic ON cc.column_id=iic.column_id"
					+ " AND iic.object_id=cc.object_id"
		          + " WHERE iic.index_id=ic.index_id and cc.object_id=o.object_id"
		          + " FOR XML PATH('')), 1, 1, '') INDEX_FIELDS"
		+ " FROM sys.objects o"
		+ " INNER JOIN sys.columns c ON o.object_id=c.object_id"
		+ " INNER JOIN sys.index_columns ic ON o.object_id=ic.object_id AND ic.column_id = c.column_id"
		+ " INNER JOIN sys.indexes i ON ic.index_id=i.index_id and i.object_id=o.object_id"
		+ " WHERE o.name='" + tableInfo.getTableName() + "'"
		+ " GROUP BY"
			+ " o.object_id,"
			+ " ic.index_id,"
			+ " o.name,"
			+ " i.name,"
			+ " CASE WHEN i.is_unique=1 THEN 'Unique' ELSE i.type_desc END"
			+ " ) temp";
		return querySql;
	}

	@Override
	public String getTableDdl(DataBaseDictionary exportParam, TableInfo tableInfo) throws Exception {
		//建表语句
		String ddl = tableInfo.getTableName();
		return ddl;
	}
}
